<?php
declare (strict_types = 1);

namespace app\controller;

use app\common\BaseController;
use app\service\GoodsService;
use app\service\HouseService;
use app\service\OrderService;
use app\service\ShowService;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\facade\Db;
use think\Request;

class ExportController extends BaseController
{
  /**
   * 导出订单信息
   * @return \think\Response
   */
  public function order(Request $request)
  {
    $startDate = $request->param('startDate', '');
    $endDate = $request->param('endDate', '');
    $month = $request->param('month', '');
    $isMonth = $request->param('isMonth', '');
    $conditions = [];
    if ($startDate) {
      $conditions[] = ['date', $startDate, '>='];
    }
    if ($endDate) {
      $conditions[] = ['date', $endDate, '<='];
    }
    if ($month && $isMonth) {
      $conditions[] = ['date', "%{$month}%", 'LIKE' ];
    }
    $order = [
      'date' => 'asc',
      'oid' => 'asc',
      'customer' => 'asc',
      'id' => 'asc'
    ];
    $data = [];
    $new = [];
    // 总计
    $all = [
      'id' => '',
      'oid' => '',
      'date' => '',
      'product' => '总计',
      'model' => '',
      'productn' => '',
      'customer' => '',
      'address' => '',
      'cname' => '',
      'phone' => '',
      'cost' => '',
      'price' => '',
      'amoumt' => '0',
      'discount' => '0',
      'tprice' => '0',
      'paid' => '0',
      'npaid' => '0',
      'unit' => '',
      'total_cost' => 0,
      'other' => '',
      'invoice' => '',
      'tax' => '',
      'send' => '',
      'installtime' => '',
      'itime' => '',
      'new' => '',
      'sendtime' => '',
      'yf' => '',
      'azf' => '',
      'js' => '',
      'ispw' => '',
      'resource' => ''
    ];
    $res = OrderService::getInstance()->getAllOrder($conditions,$order);
    foreach ($res as $resV) {
      if ($isMonth) {
        $str = $resV['date'].'-'.$resV['oid'];
        $new[$str][] = $resV;
      } else {
        $data[] = [
          'id' => $resV['id'],
          'oid' => $resV['oid'],
          'date' => $resV['date'] ? date('Y-m-d', strtotime($resV['date'])) : '',
          'product' => $resV['product'],
          'model' => $resV['model'],
          'productn' => $resV['productn'],
          'customer' => $resV['customer'],
          'address' => $resV['address'],
          'cname' => $resV['cname'],
          'phone' => $resV['phone'],
          'cost' => $resV['cost'],
          'price' => $resV['price'],
          'amoumt' => $resV['amoumt'],
          'total_cost' => round($resV['amoumt'] * $resV['cost'], 2),
          'discount' => $resV['discount'],
          'tprice' => $resV['tprice'],
          'paid' => $resV['paid'],
          'npaid' => $resV['npaid'],
          'unit' => $resV['unit'],
          'other' => $resV['other'],
          'invoice' => $resV['invoice'] ? '是' : '否',
          'tax' => $resV['tax'] ? '是' : '否',
          'send' => $resV['send'] ? '是' : '否',
          'installtime' => $resV['installtime'] && is_string($resV['installtime']) ? date('Y-m-d', strtotime($resV['installtime'])) : '',
          'itime' => $resV['itime'],
          'new' => $resV['new'] ? '新' : '旧',
          'sendtime' => $resV['sendtime'] && is_string($resV['sendtime']) ? date('Y-m-d', strtotime($resV['sendtime'])) : '',
          'yf' => $resV['yf'],
          'azf' => $resV['azf'],
          'js' => $resV['js'],
          'ispw' => $resV['ispw'] ? '是' : '否',
          'resource' => $resV['resource'] == 1 ? '展厅购买' : '订单提交'
        ];
      }
    }
    if ($isMonth) {
      foreach ($new as $newV) {
        // 合计
        $total = [
          'id' => '',
          'oid' => '',
          'date' => '',
          'product' => '小计',
          'model' => '',
          'productn' => '',
          'customer' => '',
          'address' => '',
          'cname' => '',
          'phone' => '',
          'cost' => '',
          'price' => '',
          'amoumt' => '0',
          'discount' => '0',
          'tprice' => '0',
          'paid' => '0',
          'npaid' => '0',
          'unit' => '',
          'total_cost' => 0,
          'other' => '',
          'invoice' => '',
          'tax' => '',
          'send' => '',
          'installtime' => '',
          'itime' => '',
          'new' => '',
          'sendtime' => '',
          'yf' => '',
          'azf' => '',
          'js' => '',
          'ispw' => '',
          'resource' => '',
        ];
        foreach ($newV as $newValue) {
          $data[] = [
            'id' => $newValue['id'],
            'oid' => $newValue['oid'],
            'date' => $newValue['date'] ? date('Y-m-d', strtotime($newValue['date'])) : '',
            'product' => $newValue['product'],
            'model' => $newValue['model'],
            'productn' => $newValue['productn'],
            'customer' => $newValue['customer'],
            'address' => $newValue['address'],
            'cname' => $newValue['cname'],
            'phone' => $newValue['phone'],
            'cost' => $newValue['cost'],
            'price' => $newValue['price'],
            'amoumt' => $newValue['amoumt'],
            'discount' => $newValue['discount'],
            'tprice' => $newValue['tprice'],
            'paid' => $newValue['paid'],
            'npaid' => $newValue['npaid'],
            'unit' => $newValue['unit'],
            'other' => $newValue['other'],
            'total_cost' => round($newValue['amoumt'] * $newValue['cost'], 2),
            'invoice' => $newValue['invoice'] ? '是' : '否',
            'tax' => $newValue['tax'] ? '是' : '否',
            'send' => $newValue['send'] ? '是' : '否',
            'installtime' => $newValue['installtime'] && is_string($newValue['installtime']) ? date('Y-m-d', strtotime($newValue['installtime'])) : '',
            'itime' => $newValue['itime'],
            'new' => $newValue['new'] ? '新' : '旧',
            'sendtime' => $newValue['sendtime'] && is_string($newValue['sendtime']) ? date('Y-m-d', strtotime($newValue['sendtime'])) : '',
            'yf' => $newValue['yf'],
            'azf' => $newValue['azf'],
            'js' => $newValue['js'],
            'ispw' => $newValue['ispw'] ? '是' : '否',
            'resource' => $newValue['resource'] == 1 ? '展厅购买' : '订单提交'
          ];
          $total['amoumt'] += $newValue['amoumt'];
          $total['discount'] += $newValue['discount'];
          $total['tprice'] += $newValue['tprice'];
          $total['paid'] += $newValue['paid'];
          $total['npaid'] += $newValue['npaid'];
          $total['total_cost'] += $newValue['amoumt'] * $newValue['cost'];
        }
        $data[] = $total;
        $all['amoumt'] += $total['amoumt'];
        $all['discount'] += $total['discount'];
        $all['tprice'] += $total['tprice'];
        $all['paid'] += $total['paid'];
        $all['npaid'] += $total['npaid'];
        $all['total_cost'] += $total['total_cost'];
      }
      $data[] = $all;
    }
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(15);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(15);
    $sheet->getColumnDimension('E')->setWidth(20);
    $sheet->getColumnDimension('F')->setWidth(20);
    $sheet->getColumnDimension('G')->setWidth(20);
    $sheet->getColumnDimension('H')->setWidth(20);
    $sheet->getColumnDimension('I')->setWidth(15);
    $sheet->getColumnDimension('J')->setWidth(20);
    $sheet->getColumnDimension('K')->setWidth(15);
    $sheet->getColumnDimension('L')->setWidth(15);
    $sheet->getColumnDimension('M')->setWidth(15);
    $sheet->getColumnDimension('N')->setWidth(20);
    $sheet->getColumnDimension('O')->setWidth(15);
    $sheet->getColumnDimension('P')->setWidth(15);
    $sheet->getColumnDimension('Q')->setWidth(15);
    $sheet->getColumnDimension('R')->setWidth(15);
    $sheet->getColumnDimension('S')->setWidth(15);
    $sheet->getColumnDimension('T')->setWidth(15);
    $sheet->getColumnDimension('U')->setWidth(20);
    $sheet->getColumnDimension('V')->setWidth(15);
    $sheet->getColumnDimension('W')->setWidth(15);
    $sheet->getColumnDimension('X')->setWidth(20);
    $sheet->getColumnDimension('Y')->setWidth(20);
    $sheet->getColumnDimension('Z')->setWidth(15);
    $sheet->getColumnDimension('AA')->setWidth(20);
    $sheet->getColumnDimension('AB')->setWidth(20);
    $sheet->getColumnDimension('AC')->setWidth(20);
    $sheet->getColumnDimension('AD')->setWidth(20);
    $sheet->getColumnDimension('AE')->setWidth(15);
    $sheet->getColumnDimension('AF')->setWidth(15);
    $sheet->setCellValue('A1', 'ID');
    $sheet->setCellValue('B1', '订单编号');
    $sheet->setCellValue('C1', '日期');
    $sheet->setCellValue('D1', '类别');
    $sheet->setCellValue('E1', '规格');
    $sheet->setCellValue('F1', '名称');
    $sheet->setCellValue('G1', '客户名');
    $sheet->setCellValue('H1', '送货地址');
    $sheet->setCellValue('I1', '联系人');
    $sheet->setCellValue('J1', '电话');
    $sheet->setCellValue('K1', '单价');
    $sheet->setCellValue('L1', '数量');
    $sheet->setCellValue('M1', '成本');
    $sheet->setCellValue('N1', '总成本');
    $sheet->setCellValue('O1', '优惠');
    $sheet->setCellValue('P1', '最终价格');
    $sheet->setCellValue('Q1', '已付');
    $sheet->setCellValue('R1', '应付');
    $sheet->setCellValue('S1', '单位');
    $sheet->setCellValue('T1', '备注');
    $sheet->setCellValue('U1', '是否需要发票');
    $sheet->setCellValue('V1', '是否含税');
    $sheet->setCellValue('W1', '是否已发货');
    $sheet->setCellValue('X1', '安装日期');
    $sheet->setCellValue('Y1', '安装时间');
    $sheet->setCellValue('Z1', '新旧');
    $sheet->setCellValue('AA1', '发货时间');
    $sheet->setCellValue('AB1', '邮费');
    $sheet->setCellValue('AC1', '安装费');
    $sheet->setCellValue('AD1', '结算方式');
    $sheet->setCellValue('AE1', '是否普伟客户');
    $sheet->setCellValue('AF1', '订单来源');
    $row = 1;
    foreach($data as $value){
      $row++;
      $sheet->setCellValue("A{$row}", " ".$value['id']);
      $sheet->setCellValue("B{$row}", " ".$value['oid']);
      $sheet->setCellValue("C{$row}", " ".$value['date']);
      $sheet->setCellValue("D{$row}", " ".$value['product']);
      $sheet->setCellValue("E{$row}", " ".$value['model']);
      $sheet->setCellValue("F{$row}", " ".$value['productn']);
      $sheet->setCellValue("G{$row}", " ".$value['customer']);
      $sheet->setCellValue("H{$row}", " ".$value['address']);
      $sheet->setCellValue("I{$row}", " ".$value['cname']);
      $sheet->setCellValue("J{$row}", " ".$value['phone']);
      $sheet->setCellValue("K{$row}", " ".$value['price']);
      $sheet->setCellValue("L{$row}", " ".$value['amoumt']);
      $sheet->setCellValue("M{$row}", " ".$value['cost']);
      $sheet->setCellValue("N{$row}", " ".$value['total_cost']);
      $sheet->setCellValue("O{$row}", " ".$value['discount']);
      $sheet->setCellValue("P{$row}", " ".$value['tprice']);
      $sheet->setCellValue("Q{$row}", " ".$value['paid']);
      $sheet->setCellValue("R{$row}", " ".$value['npaid']);
      $sheet->setCellValue("S{$row}", " ".$value['unit']);
      $sheet->setCellValue("T{$row}", " ".$value['other']);
      $sheet->setCellValue("U{$row}", " ".$value['invoice']);
      $sheet->setCellValue("V{$row}", " ".$value['tax']);
      $sheet->setCellValue("W{$row}", " ".$value['send']);
      $sheet->setCellValue("X{$row}", " ".$value['installtime']);
      $sheet->setCellValue("Y{$row}", " ".$value['itime']);
      $sheet->setCellValue("Z{$row}", " ".$value['new']);
      $sheet->setCellValue("AA{$row}", " ".$value['sendtime']);
      $sheet->setCellValue("AB{$row}", " ".$value['yf']);
      $sheet->setCellValue("AC{$row}", " ".$value['azf']);
      $sheet->setCellValue("AD{$row}", " ".$value['js']);
      $sheet->setCellValue("AE{$row}", " ".$value['ispw']);
      $sheet->setCellValue("AF{$row}", " ".$value['resource']);
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="慧弈明细-订单表.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 导出展厅信息
   * @return \think\Response
   */
  public function show(Request $request)
  {
    $startDate = $request->param('startDate', '');
    $endDate = $request->param('endDate', '');
    $conditions = [];
    if ($startDate) {
      $conditions[] = ['date', $startDate, '>='];
    }
    if ($endDate) {
      $conditions[] = ['date', $endDate, '<='];
    }
    $order = [
      'date' => 'asc',
      'oid' => 'asc',
      'customer' => 'asc',
      'id' => 'asc'
    ];
    $data = ShowService::getInstance()->getAllShow($conditions,$order);
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(15);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(15);
    $sheet->getColumnDimension('E')->setWidth(15);
    $sheet->getColumnDimension('F')->setWidth(20);
    $sheet->getColumnDimension('G')->setWidth(20);
    $sheet->getColumnDimension('H')->setWidth(20);
    $sheet->getColumnDimension('I')->setWidth(15);
    $sheet->getColumnDimension('J')->setWidth(20);
    $sheet->getColumnDimension('K')->setWidth(20);
    $sheet->getColumnDimension('L')->setWidth(20);
    $sheet->getColumnDimension('M')->setWidth(20);
    $sheet->getColumnDimension('N')->setWidth(15);
    $sheet->getColumnDimension('O')->setWidth(15);
    $sheet->getColumnDimension('P')->setWidth(15);
    $sheet->getColumnDimension('Q')->setWidth(20);
    $sheet->getColumnDimension('R')->setWidth(20);
    $sheet->getColumnDimension('S')->setWidth(20);
    $sheet->getColumnDimension('T')->setWidth(20);
    $sheet->setCellValue('A1', 'ID');
    $sheet->setCellValue('B1', '展览类型');
    $sheet->setCellValue('C1', '订单编号');
    $sheet->setCellValue('D1', '日期');
    $sheet->setCellValue('E1', '类别');
    $sheet->setCellValue('F1', '规格');
    $sheet->setCellValue('G1', '名称');
    $sheet->setCellValue('H1', '客户名');
    $sheet->setCellValue('I1', '送货地址');
    $sheet->setCellValue('J1', '联系人');
    $sheet->setCellValue('K1', '联系电话');
    $sheet->setCellValue('L1', '成本');
    $sheet->setCellValue('M1', '数量');
    $sheet->setCellValue('N1', '新旧');
    $sheet->setCellValue('O1', '单位');
    $sheet->setCellValue('P1', '状态');
    $sheet->setCellValue('Q1', '安装日期');
    $sheet->setCellValue('R1', '安装时间');
    $sheet->setCellValue('S1', '回仓时间');
    $sheet->setCellValue('T1', '备注');
    $row = 1;
    $stateList = ['回仓', '在展', '购买', '待定'];
    foreach($data as $value){
      $row++;
      $sheet->setCellValue("A{$row}", " ".$value['id']);
      $sheet->setCellValue("B{$row}", " ".$value['state'] == 1 ? '样品机' : '体验机');
      $sheet->setCellValue("C{$row}", " ".$value['oid']);
      $sheet->setCellValue("D{$row}", " ".$value['date'] ? date('Y-m-d', strtotime($value['date'])) : '');
      $sheet->setCellValue("E{$row}", " ".$value['product']);
      $sheet->setCellValue("F{$row}", " ".$value['model']);
      $sheet->setCellValue("G{$row}", " ".$value['productn']);
      $sheet->setCellValue("H{$row}", " ".$value['customer']);
      $sheet->setCellValue("I{$row}", " ".$value['address']);
      $sheet->setCellValue("J{$row}", " ".$value['cname']);
      $sheet->setCellValue("K{$row}", " ".$value['phone']);
      $sheet->setCellValue("L{$row}", " ".$value['cost']);
      $sheet->setCellValue("M{$row}", " ".$value['amoumt']);
      $sheet->setCellValue("N{$row}", " ".$value['new'] ? '新' : '旧');
      $sheet->setCellValue("O{$row}", " ".$value['unit']);
      $sheet->setCellValue("P{$row}", " ".$stateList[intval($value['states'])]);
      $sheet->setCellValue("Q{$row}", " ".$value['installtime'] && is_string($value['installtime']) ? date('Y-m-d', strtotime($value['installtime'])) : '');
      $sheet->setCellValue("R{$row}", " ".$value['itime']);
      $sheet->setCellValue("S{$row}", " ".$value['backtime'] && is_string($value['backtime']) ? date('Y-m-d', strtotime($value['backtime'])) : '');
      $sheet->setCellValue("T{$row}", " ".$value['other']);
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="慧弈明细-展厅表.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 导出采购列表
   * @return \think\Response
   */
  public function purchase(Request $request)
  {
    $startDate = $request->param('startDate', '');
    $endDate = $request->param('endDate', '');
    $conditions = [];
    if ($startDate) {
      $conditions[] = ['date', $startDate, '>='];
    }
    if ($endDate) {
      $conditions[] = ['date', $endDate, '<='];
    }
    $order = [
      'date' => 'asc',
      'product' => 'asc',
      'productn' => 'asc',
      'model' => 'asc',
      'id' => 'asc'
    ];
    $data = GoodsService::getInstance()->getAllGoods($conditions,$order);
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(20);
    $sheet->getColumnDimension('E')->setWidth(20);
    $sheet->getColumnDimension('F')->setWidth(20);
    $sheet->getColumnDimension('G')->setWidth(15);
    $sheet->getColumnDimension('H')->setWidth(20);
    $sheet->getColumnDimension('I')->setWidth(20);
    $sheet->getColumnDimension('J')->setWidth(20);
    $sheet->getColumnDimension('K')->setWidth(20);
    $sheet->getColumnDimension('L')->setWidth(20);
    $sheet->getColumnDimension('M')->setWidth(15);
    $sheet->getColumnDimension('N')->setWidth(20);
    $sheet->getColumnDimension('O')->setWidth(20);
    $sheet->getColumnDimension('P')->setWidth(20);
    $sheet->getColumnDimension('Q')->setWidth(20);
    $sheet->setCellValue('A1', 'ID');
    $sheet->setCellValue('B1', '订单时间');
    $sheet->setCellValue('C1', '类别');
    $sheet->setCellValue('D1', '产品名称');
    $sheet->setCellValue('E1', '规格');
    $sheet->setCellValue('F1', '数量');
    $sheet->setCellValue('G1', '单位');
    $sheet->setCellValue('H1', '单价');
    $sheet->setCellValue('I1', '仓库价格');
    $sheet->setCellValue('J1', '进货金额');
    $sheet->setCellValue('K1', '实付');
    $sheet->setCellValue('L1', '应付');
    $sheet->setCellValue('M1', '入库状态');
    $sheet->setCellValue('N1', '备注');
    $sheet->setCellValue('O1', '入仓时间');
    $sheet->setCellValue('P1', '结算方式');
    $sheet->setCellValue('Q1', '供应商');
    $row = 1;
    foreach($data as $value){
      $row++;
      $sheet->setCellValue("A{$row}", " ".$value['id']);
      $sheet->setCellValue("B{$row}", " ".$value['date'] ? date('Y-m-d', strtotime($value['date'])) : '');
      $sheet->setCellValue("C{$row}", " ".$value['product']);
      $sheet->setCellValue("D{$row}", " ".$value['productn']);
      $sheet->setCellValue("E{$row}", " ".$value['model']);
      $sheet->setCellValue("F{$row}", " ".$value['amoumt']);
      $sheet->setCellValue("G{$row}", " ".$value['unit']);
      $sheet->setCellValue("H{$row}", " ".$value['price']);
      $sheet->setCellValue("I{$row}", " ".$value['housecost']);
      $sheet->setCellValue("J{$row}", " ".$value['total']);
      $sheet->setCellValue("K{$row}", " ".$value['paid']);
      $sheet->setCellValue("L{$row}", " ".$value['notpaid']);
      $sheet->setCellValue("M{$row}", " ".$value['inhouse'] ? '已入库' : '未入库');
      $sheet->setCellValue("N{$row}", " ".$value['other']);
      $sheet->setCellValue("O{$row}", " ".$value['installtime'] && is_string($value['installtime']) ? date('Y-m-d', strtotime($value['installtime'])) : '');
      $sheet->setCellValue("P{$row}", " ".$value['js']);
      $sheet->setCellValue("Q{$row}", " ".$value['gys']);
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="慧弈明细-采购表.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 导出月底仓库记录列表
   * @return \think\Response
   */
  public function house(Request $request)
  {
    $month = $request->param('month', '');
    $isMonth = $request->param('isMonth', 0);
    if (!$month && $isMonth) {
      $this->send400Response('请选择月份');
    }
    // 是否月底导出
    if ($isMonth) {
      $data = Db::table('house_statistics')
        ->alias('hs')
        ->join('house h', 'h.id = hs.hid', 'LEFT')
        ->field('h.product, h.productn, h.model, h.unit, hs.*')
        ->where('date', 'LIKE', "{$month}%")
        ->order('product', 'desc')
        ->order('productn', 'desc')
        ->order('id', 'desc')
        ->select()
        ->toArray();
    } else {
      $order = ['product' => 'desc', 'productn' => 'desc', 'id' => 'desc'];
      $data = HouseService::getInstance()->getAllHouse([], $order);
    }
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(15);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(20);
    $sheet->getColumnDimension('E')->setWidth(15);
    $sheet->getColumnDimension('F')->setWidth(15);
    $sheet->getColumnDimension('G')->setWidth(15);
    $sheet->getColumnDimension('H')->setWidth(15);
    $sheet->getColumnDimension('I')->setWidth(15);
    $sheet->getColumnDimension('J')->setWidth(15);
    $sheet->getColumnDimension('K')->setWidth(20);
    $sheet->getColumnDimension('L')->setWidth(20);
    $sheet->setCellValue('A1', '日期');
    $sheet->setCellValue('B1', '类别');
    $sheet->setCellValue('C1', '名称');
    $sheet->setCellValue('D1', '规格');
    $sheet->setCellValue('E1', '单位');
    $sheet->setCellValue('F1', '全新数量');
    $sheet->setCellValue('G1', '待出库数量');
    $sheet->setCellValue('H1', '旧品数量');
    $sheet->setCellValue('I1', '旧品待出库数量');
    $sheet->setCellValue('J1', '损坏数量');
    $sheet->setCellValue('K1', '成本');
    $sheet->setCellValue('L1', '总价值');
    $row = 1;
    foreach($data as $value){
      $row++;
      $total = $value['nowcost'] * ($value['amoumt'] + $value['wamoumt'] + $value['oldamoumt'] + $value['oldwamoumt']);
      $date = $isMonth ? date('Y-m', strtotime($value['date'])) : date('Y-m-d');
      $sheet->setCellValue("A{$row}", " ".$date);
      $sheet->setCellValue("B{$row}", " ".$value['product']);
      $sheet->setCellValue("C{$row}", " ".$value['productn']);
      $sheet->setCellValue("D{$row}", " ".$value['model']);
      $sheet->setCellValue("E{$row}", " ".$value['unit']);
      $sheet->setCellValue("F{$row}", " ".$value['amoumt']);
      $sheet->setCellValue("G{$row}", " ".$value['wamoumt']);
      $sheet->setCellValue("H{$row}", " ".$value['oldamoumt']);
      $sheet->setCellValue("I{$row}", " ".$value['oldwamoumt']);
      $sheet->setCellValue("J{$row}", " ".$value['badamoumt']);
      $sheet->setCellValue("K{$row}", " ".$value['nowcost']);
      $sheet->setCellValue("L{$row}", " ".round($total, 2));
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="慧弈明细-仓库现存.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 导出商品进出统计
   * @return \think\Response
   */
  public function inout(Request $request) {
    $month = $request->param('month', '');
    if (!$month) {
      $this->send400Response('请选择月份');
    }
    // ==== 上月的仓库存量 ====
    $lastMonthHouse = [];
    $lastMonth = date('Y-m', strtotime('-1 month', strtotime($month)));
    $res = Db::table('house_statistics')
      ->alias('hs')
      ->join('house h', 'h.id = hs.hid', 'LEFT')
      ->field('h.product, h.productn, h.model, h.unit, hs.*')
      ->where('date', 'LIKE', "{$lastMonth}%")
      ->order('product', 'desc')
      ->order('productn', 'desc')
      ->order('id', 'desc')
      ->select()
      ->toArray();
    foreach ($res as $value) {
      $lastRes[] = [
        'date' => $lastMonth,
        'productn' => $value['productn'],
        'model' => $value['model'],
        'cost' => $value['nowcost'],
        'amoumt' => $value['amoumt'] + $value['wamoumt'] + $value['oldamoumt'] + $value['oldwamoumt'],
        'total' => $value['nowcost'] * ($value['amoumt'] + $value['wamoumt'] + $value['oldamoumt'] + $value['oldwamoumt']),
        'sell_total' => ''
      ];
    }
    $lastRes = array_values($lastRes);
    //获取合计
    $lastMonthHouseTotal = [
      'date' => $lastMonth,
      'productn' => '合计(上月库存)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => ''
    ];
    foreach ($lastRes as $sellV) {
      $lastMonthHouseTotal['amoumt'] += $sellV['amoumt'];
      $lastMonthHouseTotal['total'] += $sellV['total'];
    }
    $lastMonthHouse[] = $lastMonthHouseTotal;
    $lastMonthHouse[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];
    // ==== 该月的销售发出 ====
    $sellOut = [];
    $conditions = [];
    if ($month) {
      $conditions[] = ['sendtime', "{$month}%", 'LIKE'];
      $conditions[] = ['send', 1, '='];
    }
    $conditions[] = ['resource', '0', '='];
    $order = [
      'date' => 'asc',
      'productn' => 'asc',
      'model' => 'asc',
      'id' => 'asc'
    ];
    $res1 = OrderService::getInstance()->getAllOrder($conditions, $order);

    foreach ($res1 as $value1) {
      $sku = $value1['productn'].'-'.$value1['model'].'-'.$value1['cost'];
      if (isset($sellOut[$sku])) {
        $sellOut[$sku]['amoumt'] += $value1['amoumt'];
        $sellOut[$sku]['total'] += round($value1['amoumt'] * $value1['cost'], 2);
        $sellOut[$sku]['sell_total'] += $value1['tprice'];
      } else {
        $sellOut[$sku] = [
          'date' => $month,
          'productn' => $value1['productn'],
          'model' => $value1['model'],
          'cost' => $value1['cost'],
          'amoumt' => $value1['amoumt'],
          'total' => round($value1['amoumt'] * $value1['cost'], 2),
          'sell_total' => $value1['tprice']
        ];
      }
    }
    $sellOut = array_values($sellOut);
    //获取合计
    $sellOutTotal = [
      'date' => $month,
      'productn' => '合计(销售发出)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => 0
    ];
    foreach ($sellOut as $sellV) {
      $sellOutTotal['amoumt'] += $sellV['amoumt'];
      $sellOutTotal['total'] += $sellV['total'];
      $sellOutTotal['sell_total'] += $sellV['sell_total'];
    }
    $sellOut[] = $sellOutTotal;
    $sellOut[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];

    // ==== 展厅购买 ====
    $showOut = [];
    $conditions = [];
    if ($month) {
      $conditions[] = ['sendtime', "%{$month}%", 'LIKE'];
      $conditions[] = ['send', 1, '='];
    }
    $conditions[] = ['resource', '1', '='];
    $res2 = OrderService::getInstance()->getAllOrder($conditions, $order);
    foreach ($res2 as $value2) {
      $sku = $value2['productn'].'-'.$value2['model'].'-'.$value2['cost'];
      if (isset($showOut[$sku])) {
        $showOut[$sku]['amoumt'] += $value2['amoumt'];
        $showOut[$sku]['total'] += round($value2['amoumt'] * $value2['cost'], 2);
        $showOut[$sku]['sell_total'] += $value2['tprice'];
      } else {
        $showOut[$sku] = [
          'date' => $month,
          'productn' => $value2['productn'],
          'model' => $value2['model'],
          'cost' => $value2['cost'],
          'amoumt' => $value2['amoumt'],
          'total' => round($value2['amoumt'] * $value2['cost'], 2),
          'sell_total' => $value2['tprice']
        ];
      }
    }
    $showOut = array_values($showOut);
    //获取合计
    $showOutTotal = [
      'date' => $month,
      'productn' => '合计(展厅购买)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => 0
    ];
    foreach ($showOut as $sellV) {
      $showOutTotal['amoumt'] += $sellV['amoumt'];
      $showOutTotal['total'] += $sellV['total'];
      $showOutTotal['sell_total'] += $sellV['sell_total'];
    }
    $showOut[] = $showOutTotal;
    $showOut[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];

    // ==== 仓库发到展厅 ====
    $houseToShow = [];
    $conditions = [];
    if ($month) {
      $conditions[] = ['date', "%{$month}%", 'LIKE'];
    }
    $res3 = ShowService::getInstance()->getAllShow($conditions, $order);
    foreach ($res3 as $value3) {
      $sku = $value3['productn'].'-'.$value3['model'].'-'.$value3['cost'];
      if (isset($houseToShow[$sku])) {
        $houseToShow[$sku]['amoumt'] += $value3['amoumt'];
        $houseToShow[$sku]['total'] += round($value3['amoumt'] * $value3['cost'], 2);
        $houseToShow[$sku]['sell_total'] += $value3['tprice'];
      } else {
        $houseToShow[$sku] = [
          'date' => $month,
          'productn' => $value3['productn'],
          'model' => $value3['model'],
          'cost' => $value3['cost'],
          'amoumt' => $value3['amoumt'],
          'total' => round($value3['amoumt'] * $value3['cost'], 2),
          'sell_total' => $value3['tprice']
        ];
      }
    }
    $houseToShow = array_values($houseToShow);
    //获取合计
    $houseToShowTotal = [
      'date' => $month,
      'productn' => '合计(仓库发展厅)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => 0
    ];
    foreach ($houseToShow as $toShowV) {
      $houseToShowTotal['amoumt'] += $toShowV['amoumt'];
      $houseToShowTotal['total'] += $toShowV['total'];
      $houseToShowTotal['sell_total'] += $toShowV['sell_total'];
    }
    $houseToShow[] = $houseToShowTotal;
    $houseToShow[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];

    // ==== 展厅发向仓库 ====
    $showToHouse = [];
    $conditions = [
      ['states', 0, '=']
    ];
    if ($month) {
      $conditions[] = ['backtime', "%{$month}%", 'LIKE'];
    }
    $res4 = ShowService::getInstance()->getAllShow($conditions, $order);
    foreach ($res4 as $value4) {
      $sku = $value4['productn'].'-'.$value4['model'].'-'.$value4['cost'];
      if (isset($showToHouse[$sku])) {
        $showToHouse[$sku]['amoumt'] += $value4['amoumt'];
        $showToHouse[$sku]['total'] += round($value4['amoumt'] * $value4['cost'], 2);
        $showToHouse[$sku]['sell_total'] += $value4['tprice'];
      } else {
        $showToHouse[$sku] = [
          'date' => $month,
          'productn' => $value4['productn'],
          'model' => $value4['model'],
          'cost' => $value4['cost'],
          'amoumt' => $value4['amoumt'],
          'total' => round($value4['amoumt'] * $value4['cost'], 2),
          'sell_total' => $value4['tprice']
        ];
      }
    }
    $showToHouse = array_values($showToHouse);
    //获取合计
    $showToHouseTotal = [
      'date' => $month,
      'productn' => '合计(展厅发向仓库)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => 0
    ];
    foreach ($showToHouse as $toShowV) {
      $showToHouseTotal['amoumt'] += $toShowV['amoumt'];
      $showToHouseTotal['total'] += $toShowV['total'];
      $showToHouseTotal['sell_total'] += $toShowV['sell_total'];
    }
    $showToHouse[] = $showToHouseTotal;
    $showToHouse[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];

    // ==== 展厅库存 ====
    $show = [];
    $res5 = Db::table('show_statistics')
      ->where('date', 'LIKE', "%{$month}%")
      ->order('date')
      ->order('productn')
      ->order('model')
      ->order('id')
      ->select()
      ->toArray();
    //获取合计
    $showTotal = [
      'date' => $month,
      'productn' => '合计(展厅库存)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => 0
    ];
    foreach ($res5 as $showV) {
      $show[] = [
        'date' => $month,
        'productn' => $showV['productn'],
        'model' => $showV['model'],
        'cost' => $showV['cost'],
        'amoumt' => $showV['amoumt'],
        'total' => round($showV['amoumt'] * $showV['cost'], 2),
        'sell_total' => 0
      ];
      $showTotal['amoumt'] += $showV['amoumt'];
      $showTotal['total'] += round($showV['amoumt'] * $showV['cost'], 2);
    }
    $show[] = $showTotal;
    $show[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];

    // ==== 仓库购买 ====
    $housePurchase = [];
    $conditions = [];
    if ($month) {
      $conditions[] = ['inhouse', 1, '='];
      $conditions[] = ['installtime', "%{$month}%", 'LIKE'];
    }
    $res6 = GoodsService::getInstance()->getAllGoods($conditions, $order);
    foreach ($res6 as $value6) {
      $sku = $value6['productn'].'-'.$value6['model'].'-'.$value6['price'];
      if (isset($housePurchase[$sku])) {
        $housePurchase[$sku]['amoumt'] += $value6['amoumt'];
        $housePurchase[$sku]['total'] += round($value6['amoumt'] * $value6['price'], 2);
      } else {
        $housePurchase[$sku] = [
          'date' => $month,
          'productn' => $value6['productn'],
          'model' => $value6['model'],
          'cost' => $value6['price'],
          'amoumt' => $value6['amoumt'],
          'total' => round($value6['amoumt'] * $value6['price'], 2),
          'sell_total' => 0
        ];
      }
    }
    $housePurchase = array_values($housePurchase);
    //获取合计
    $housePurchaseTotal = [
      'date' => $month,
      'productn' => '合计(仓库购买)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => 0
    ];
    foreach ($housePurchase as $toShowV) {
      $housePurchaseTotal['amoumt'] += $toShowV['amoumt'];
      $housePurchaseTotal['total'] += $toShowV['total'];
    }
    $housePurchase[] = $housePurchaseTotal;
    $housePurchase[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];

    // ==== 当月库存 ====
    $monthHouse = [];
    $monthRes = [];
    // 是否月底导出
    if (date('Y-m', strtotime($month)) === date('Y-m')) {
      $order = ['product' => 'desc', 'productn' => 'desc', 'id' => 'desc'];
      $res7 = HouseService::getInstance()->getAllHouse([], $order);
    } else {
      $res7 = Db::table('house_statistics')
        ->alias('hs')
        ->join('house h', 'h.id = hs.hid', 'LEFT')
        ->field('h.product, h.productn, h.model, h.unit, hs.*')
        ->where('date', 'LIKE', "{$month}%")
        ->order('product', 'desc')
        ->order('productn', 'desc')
        ->order('id', 'desc')
        ->select()
        ->toArray();
    }
    foreach ($res7 as $value7) {
      $monthRes[] = [
        'date' => $month,
        'productn' => $value7['productn'],
        'model' => $value7['model'],
        'cost' => $value7['nowcost'],
        'amoumt' => $value7['amoumt'] + $value7['wamoumt'] + $value7['oldamoumt'] + $value7['oldwamoumt'],
        'total' => $value7['nowcost'] * ($value7['amoumt'] + $value7['wamoumt'] + $value7['oldamoumt'] + $value7['oldwamoumt']),
        'sell_total' => ''
      ];
    }
    $monthRes = array_values($monthRes);
    //获取合计
    $monthHouseTotal = [
      'date' => $month,
      'productn' => '合计(当月库存)',
      'model' => '',
      'cost' => '',
      'amoumt' => 0,
      'total' => 0,
      'sell_total' => ''
    ];
    foreach ($monthRes as $sellV) {
      $monthHouseTotal['amoumt'] += $sellV['amoumt'];
      $monthHouseTotal['total'] += $sellV['total'];
    }
    $monthHouse[] = $monthHouseTotal;
    $monthHouse[] = [
      'date' => '----',
      'productn' => '----',
      'model' => '----',
      'cost' => '----',
      'amoumt' => '----',
      'total' => '----',
      'sell_total' => '----'
    ];
    // 导出数据
    $data = array_merge($lastMonthHouse, $sellOut, $showOut, $houseToShow, $showToHouse, $show, $housePurchase, $monthHouse);
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(15);
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(15);
    $sheet->getColumnDimension('E')->setWidth(15);
    $sheet->getColumnDimension('F')->setWidth(20);
    $sheet->getColumnDimension('G')->setWidth(20);
    $sheet->setCellValue('A1', '年月');
    $sheet->setCellValue('B1', '产品');
    $sheet->setCellValue('C1', '规格');
    $sheet->setCellValue('D1', '单成本');
    $sheet->setCellValue('E1', '数量');
    $sheet->setCellValue('F1', '总成本');
    $sheet->setCellValue('G1', '售价');
    $row = 1;
    foreach($data as $value){
      $row++;
      $sheet->setCellValue("A{$row}", " ".$value['date']);
      $sheet->setCellValue("B{$row}", " ".$value['productn']);
      $sheet->setCellValue("C{$row}", " ".$value['model']);
      $sheet->setCellValue("D{$row}", " ".$value['cost']);
      $sheet->setCellValue("E{$row}", " ".$value['amoumt']);
      $sheet->setCellValue("F{$row}", " ".$value['total']);
      $sheet->setCellValue("G{$row}", " ".$value['sell_total']);
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="慧弈明细-商品进出统计.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }
}
